﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using DevExpress.Web.Mvc;
using Webcar.BLL;
using Webcar.Entitys;
using Webcar.Utils;
using EntityFramework.Extensions;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
namespace Webcar.Controllers
{
    public partial class DriverController : BaseControler
    {
        [HttpGet]
        public ActionResult StudyList()
        {
            ViewBag.StudyMonth = DateTime.Now.ToString("yyyy-MM");
            return View();
        }
        [HttpPost]
        public ActionResult StudyList(string dept,string ny)
        {
            if (dept.IsNull() || ny.IsNull())
            {
                return View();
            }
            

            var list = from p in WebcarDataProvider.DB.BS_EMP
                       where p.DEPT_CODE == dept && p.IS_USE == "T"
                       select p;
            ViewBag.AllStudents = list.ToArray();
            var study_list = from p in WebcarDataProvider.DB.BS_EMP
                             join p2 in WebcarDataProvider.DB.SEC_DRV_STUDY
                             on p.MAN_CODE equals p2.MAN_CODE
                             where p.DEPT_CODE == dept && p.IS_USE == "T" && p2.NY == ny
                             select p2.MAN_CODE;
            ViewBag.Students = study_list.ToList();
            return View();
        }
        [HttpPost]
        public ActionResult SaveStudy(string dept, string ny)
        {
            CurrentUser.EnsurePermission(PermissionManager.编辑驾驶安全学习);
            using (TransactionScope scope = new TransactionScope())
            {
                using (var DB = new WebcarContext())
                {
                    string[] selected_values = CheckBoxListExtension.GetSelectedValues<string>("chkStudyList");
                    string sql = "delete from sec_drv_study where ny = '" + ny + "' and " +
                        " man_code in (select man_code from bs_emp where dept_code='" + dept + "')";

                    DB.ObjectContext.ExecuteStoreCommand(sql);

                    foreach (var item in selected_values)
                    {
                        SEC_DRV_STUDY study = new SEC_DRV_STUDY();
                        study.MAN_CODE = item;
                        study.NY = ny;
                        study.IS_STUDY = "T";
                        study.DEPT_CODE = dept;
                        DB.SEC_DRV_STUDY.Add(study);
                    }
                    DB.SaveChanges();
                }
                scope.Complete();
            }
            return Success("StudyList", "Driver");
        }


        public ActionResult StudySummary(string dept,string ny)
        {
            if (dept.IsNull() || ny.IsNull())
            {
                ViewBag.StudyMonth = DateTime.Now.Year.ToString();
                return View();
            }
            string sql = @"select man_code,man_name,dept_code,
                case sum(case ny when @year+'-01' then is_study end) when 1 then '已学习' else '未学习' end month_01,
                case sum(case ny when @year+'-02' then is_study end) when 1 then '已学习' else '未学习' end month_02,
                case sum(case ny when @year+'-03' then is_study end) when 1 then '已学习' else '未学习' end month_03,
                case sum(case ny when @year+'-04' then is_study end) when 1 then '已学习' else '未学习' end month_04,
                case sum(case ny when @year+'-05' then is_study end) when 1 then '已学习' else '未学习' end month_05,
                case sum(case ny when @year+'-06' then is_study end) when 1 then '已学习' else '未学习' end month_06,
                case sum(case ny when @year+'-07' then is_study end) when 1 then '已学习' else '未学习' end month_07,
                case sum(case ny when @year+'-08' then is_study end) when 1 then '已学习' else '未学习' end month_08,
                case sum(case ny when @year+'-09' then is_study end) when 1 then '已学习' else '未学习' end month_09,
                case sum(case ny when @year+'-10' then is_study end) when 1 then '已学习' else '未学习' end month_10,
                case sum(case ny when @year+'-11' then is_study end) when 1 then '已学习' else '未学习' end month_11,
                case sum(case ny when @year+'-12' then is_study end) when 1 then '已学习' else '未学习' end month_12
                from (
	                select a.ny,a.man_code,a.man_name,a.dept_code,case isnull(b.is_study,'F') when 'T' then 1 else 0 end is_study
	                from 
	                (select a.ny,b.man_code,b.man_name,b.dept_code from 
                bs_emp b join 
		                (select @year+'-01' ny 
		                union all
		                select @year+'-02' 
		                union all
		                select @year+'-03' 
		                union all
		                select @year+'-04' 
		                union all
		                select @year+'-05' 
		                union all
		                select @year+'-06' 
		                union all
		                select @year+'-07' 
		                union all
		                select @year+'-08' 
		                union all
		                select @year+'-09' 
		                union all
		                select @year+'-10' 
		                union all
		                select @year+'-11' 
		                union all
		                select @year+'-12' 
		                ) as a  on b.is_use='T' and b.dept_code=@dept 
	                ) a left join
	                sec_drv_study b
	                on a.ny=b.ny and a.man_code = b.man_code
	                and @year=left(b.ny,4)
	                ) as t
                group by man_code,man_name,dept_code";
            SqlParameter para1 = new SqlParameter("@dept", dept);
            SqlParameter para2 = new SqlParameter("@year", ny);

            DataTable table = WebcarDataProvider.DB.ExecuteDataTable(sql, para1, para2);
            return View(model:table);
        }
    }
}